""""
Case Type   : D库常用语法兼容-proc
Case Name   : 执行创建proc操作后gs_dump备份恢复后再查看当前存储过程
Create At   : 2025-11-19
Owner       : chen
Description :
    1.创建proc
    2.rename proc
    3.rename procedure
    4.gs_dump备份数据库
    5.恢复数据库
    6.查看proc状态
    7.删除不存在的存储过程
    8.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.合理报错
    8.成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class DDL_Proc_Case0026(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.dbname_d = Node('PrimaryDbDmode').db_name
        self.proc_name = 'p_proc_case026'
        self.new_proc_name = 'p_proc_new_case026'
        self.backup_path = os.path.join(
            os.path.dirname(macro.DB_INSTANCE_PATH),
                                        'dump_proc_042.tar')

    def test_proc(self):
        text = '-----step1: 创建proc表,并插入数据; expect: 成功-----'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_proc = f"""create proc {self.proc_name}()
            is
            begin
            RAISE INFO 'call procedure: {self.proc_name}';
            end;"""
        res = primary_sh.execut_db_sql(create_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, res)
        detail_sql = f'\df {self.proc_name}();'
        res = primary_sh.execut_db_sql(detail_sql, dbname=self.dbname_d)
        log.info(res)
        detail_list = ['public', self.proc_name, 'void', 'normal', 'f', 'p']
        for detail in detail_list:
            self.assertIn(detail, res)
        find_sql = f"select provolatile from pg_proc where proname =" \
            f" '{self.proc_name}';"
        res = primary_sh.execut_db_sql(find_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn('v', res)

        text = '-----step2: rename proc; expect: 成功-----'
        log.info(text)
        alter_sql = f'alter proc {self.proc_name}() rename to ' \
            f'{self.new_proc_name};'
        res = primary_sh.execut_db_sql(alter_sql, dbname=self.dbname_d)
        self.assertIn(self.constant.ALTER_FUNCTION_SUCCESS_MSG, res)
        detail_sql = f'\df {self.new_proc_name}();'
        res = primary_sh.execut_db_sql(detail_sql, dbname=self.dbname_d)
        log.info(res)
        detail_list = ['public', self.new_proc_name, 'void', 'normal', 'f', 'p']
        for detail in detail_list:
            self.assertIn(detail, res)
        alter_sql = f'alter proc {self.new_proc_name}() rename to ' \
            f'{self.proc_name};'
        res = primary_sh.execut_db_sql(alter_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.ALTER_FUNCTION_SUCCESS_MSG, res)

        text = '-----step3: rename procedure; expect: 成功-----'
        log.info(text)
        alter_sql = f'alter procedure {self.proc_name}() rename to ' \
            f'{self.new_proc_name};'
        res = primary_sh.execut_db_sql(alter_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.ALTER_FUNCTION_SUCCESS_MSG, res)
        alter_sql = f'alter procedure {self.new_proc_name}() rename to ' \
            f'{self.proc_name};'
        res = primary_sh.execut_db_sql(alter_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.ALTER_FUNCTION_SUCCESS_MSG, res)

        text = '-----step4: gs_dump备份数据库; expect: 成功-----'
        log.info(text)
        result = primary_sh.exec_gs_dump(filename=self.backup_path, 
                            dbname=self.dbname_d)
        self.assertTrue(result, '执行失败:' + text)

        text = '-----step5: 恢复数据库; expect: 成功-----'
        log.info(text)
        result = primary_sh.restore_file(self.backup_path,
                                              dbname=self.dbname_d)
        log.info(result)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG,
                      result, '执行失败' + text)
        
        text = '-----step6: 恢复数据库; expect: 成功-----'
        log.info(text)
        detail_sql = f'\df {self.proc_name}();'
        res = primary_sh.execut_db_sql(detail_sql, dbname=self.dbname_d)
        log.info(res)
        detail_list = ['public', self.proc_name, 'void', 'normal', 'f', 'p']
        for detail in detail_list:
            self.assertIn(detail, res)
        find_sql = f"select provolatile from pg_proc where proname =" \
            f" '{self.proc_name}';"
        res = primary_sh.execut_db_sql(find_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn('v', res)

        text = '-----step7: 删除不存在的存储过程; expect: 成功-----'
        log.info(text)
        drop_sql = f'drop proc {self.new_proc_name}();'
        res = primary_sh.execut_db_sql(drop_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(f'{self.new_proc_name} does not exist', res)

    def tearDown(self):
        text = '-----step4: 清理环境; expect: 成功-----'
        log.info(text)
        drop_proc = f'''drop proc if exists {self.proc_name}();
                drop proc if exists {self.new_proc_name}();'''
        res = primary_sh.execut_db_sql(drop_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_PROCEDURE_SUCCESS_MSG, res)